use std::{
    collections::HashMap,
    fs::{remove_file, OpenOptions},
    io::{Error, Write},
    path::Path,
    time::{SystemTime, UNIX_EPOCH},
};

use crate::{
    db::models::{Contract, Invoice, Material, Payment},
    schema::{
        contract,
        material::{self},
        provider::dsl::*,
    },
};

use actix_multipart::Multipart;
use actix_web::{post, web::Data, HttpResponse};
use diesel::{
    insert_into, query_dsl::methods::FilterDsl, Connection, ExpressionMethods, RunQueryDsl,
};
use futures::StreamExt;

use crate::{
    db::models::{Project, Provider},
    schema::{project, provider},
    DbPool,
};

#[post("/import_project_from_file")]
async fn import_project_from_file(db: Data<DbPool>, mut payload: Multipart) -> HttpResponse {
    let a = payload.next().await;
    if let Some(field) = a {
        match field {
            Ok(mut f) => {
                let file_name = format!(
                    "import_{:?}.xlsx",
                    SystemTime::now()
                        .duration_since(UNIX_EPOCH)
                        .unwrap()
                        .as_millis()
                );
                let mut bytes = vec![];
                loop {
                    if let Some(Ok(content)) = f.next().await {
                        bytes.push(content);
                    } else {
                        break;
                    }
                }
                match save_file(file_name.clone(), bytes.concat()) {
                    Ok(_) => {
                        let conf = Conf::new(
                            1,
                            1,
                            vec![
                                ("category", "类别"),
                                ("contract_number", "合同编号"),
                                ("contract_amount", "合同金额"),
                                ("material_amount", "材料款"),
                                ("material_verify_amount", "结算/对账金额"),
                                ("payment_amount", "合计付款"),
                                ("provider_name", "材料商名称"),
                                ("invoice_amount", "合计已开票"),
                                ("comment", "备注"),
                                ("his_defaulters_amount", "欠款"),
                                ("his_hang_invoice_amount", "合计已挂票"),
                            ],
                        );
                        let excel_projects =
                            parse_excel_projects(&file_name, &conf).expect("read file error");
                        println!("projects: {:?}", excel_projects);
                        match excel_projects_to_db_data(&excel_projects, db) {
                            Ok(()) => {
                                delete_file(file_name);
                                return HttpResponse::Ok().json(true);
                            }
                            Err(e) => {
                                println!("import error: {}", e.to_string());
                                return HttpResponse::BadRequest().json(false);
                            }
                        }
                    }

                    _ => {
                        return HttpResponse::BadRequest().json(false);
                    }
                }
            }
            _ => {
                return HttpResponse::BadRequest().json(false);
            }
        }
    }
    return HttpResponse::BadRequest().json(false);
}

fn save_file(file_name: String, content: Vec<u8>) -> Result<(), Error> {
    let file_path = Path::new("./").join(file_name);
    let mut f = OpenOptions::new()
        .append(true)
        .write(true)
        .create(true)
        .open(&file_path)
        .unwrap();
    f.write_all(&content)
}

fn delete_file(file_name: String) {
    let file_path = Path::new("./").join(file_name);
    match remove_file(file_path) {
        Err(e) => {
            println!("delete file failed: {}", e);
        }
        _ => (),
    }
}

fn parse_excel_projects(path: &str, conf: &Conf) -> Result<Vec<ExcelProject>, String> {
    let mut projects = vec![];
    if let Ok(mut wb) = xl::Workbook::open(path) {
        let sheets = wb.sheets();
        let sheet_names = sheets.by_name();
        println!("sheet names: {:?}", sheet_names);
        for i in 0..sheet_names.len() {
            if i + 1 < conf.start_sheet {
                println!("skip sheet {}", sheet_names[i]);
                continue;
            }
            let index = i + 1;
            if let Some(sheet) = sheets.get(index) {
                println!("parse sheet: {}, index: {}", sheet.name, index);
                let mut records = vec![];
                let mut title_idx_map = HashMap::new();
                let mut latest_category = "".to_string();
                for row in sheet.rows(&mut wb) {
                    println!("row.idx: {}", row.1);
                    if row.1 < conf.start_row {
                        continue;
                    }
                    if row.1.eq(&conf.start_row) {
                        // 标题行
                        println!(
                            "title cell, len:{}, data : {:?}",
                            row.0.len(),
                            row.0
                                .iter()
                                .map(|c| c.value.to_string().clone())
                                .collect::<Vec<String>>()
                        );
                        for idx in 0..row.0.len() {
                            let cell = &row.0[idx];
                            match check_title_mapping(
                                &trim_string(cell.value.to_string().clone()),
                                conf,
                            ) {
                                Ok(k) => {
                                    title_idx_map.insert(k, idx);
                                }
                                Err(_) => (),
                            }
                        }
                        println!("title_idx_map: {:?}", title_idx_map);
                        if title_idx_map.len() != conf.column_mapping.len() {
                            println!("invalid title idx mapping, please check your excel file");
                            return Err("invalid title idx mapping, please check your excel file"
                                .to_string());
                        }
                    } else {
                        // 数据行
                        // println!(
                        //     "data cell, len:{}, data : {:?}",
                        //     row.0.len(),
                        //     row.0
                        //         .iter()
                        //         .map(|c| c.value.to_string().clone())
                        //         .collect::<Vec<String>>()
                        // );
                        let the_category = &row.0[title_idx_map.get("category").unwrap().clone()];
                        let contract_number =
                            &row.0[title_idx_map.get("contract_number").unwrap().clone()];
                        let contract_amount =
                            &row.0[title_idx_map.get("contract_amount").unwrap().clone()];
                        let material_amount =
                            &row.0[title_idx_map.get("material_amount").unwrap().clone()];
                        let material_verify_amount =
                            &row.0[title_idx_map.get("material_verify_amount").unwrap().clone()];
                        let payment_amount =
                            &row.0[title_idx_map.get("payment_amount").unwrap().clone()];
                        let provider_name =
                            &row.0[title_idx_map.get("provider_name").unwrap().clone()];
                        let invoice_amount =
                            &row.0[title_idx_map.get("invoice_amount").unwrap().clone()];
                        let comment = &row.0[title_idx_map.get("comment").unwrap().clone()];
                        let his_defaulters_amount =
                            &row.0[title_idx_map.get("his_defaulters_amount").unwrap().clone()];
                        let his_hang_invoice_amount = &row.0[title_idx_map
                            .get("his_hang_invoice_amount")
                            .unwrap()
                            .clone()];
                        let mut category_val = parse_cell_string(the_category.value.to_string());
                        if category_val.eq("") {
                            category_val = latest_category.clone();
                        } else {
                            latest_category = category_val.clone();
                        }

                        let record = ExcelRecord {
                            category: category_val,
                            contract_number: parse_cell_string(contract_number.value.to_string()),
                            contract_amount: parse_cell_f64(contract_amount.value.to_string()),
                            material_amount: parse_cell_f64(material_amount.value.to_string()),
                            material_verify_amount: parse_cell_f64(
                                material_verify_amount.value.to_string(),
                            ),
                            payment_amount: parse_cell_f64(payment_amount.value.to_string()),
                            provider_name: parse_cell_string(provider_name.value.to_string()),
                            invoice_amount: parse_cell_f64(invoice_amount.value.to_string()),
                            comment: parse_cell_string(comment.value.to_string()),
                            his_defaulters_amount: parse_cell_f64(
                                his_defaulters_amount.value.to_string(),
                            ),
                            his_hang_invoice_amount: parse_cell_f64(
                                his_hang_invoice_amount.value.to_string(),
                            ),
                        };
                        if record.contract_number.eq("") {
                            println!("skip row for empty contract_number");
                            continue;
                        }
                        // println!("record: {:?}", record);
                        records.push(record);
                    }
                }

                let excel_project = ExcelProject {
                    project_name: sheet.name.clone(),
                    records,
                };
                projects.push(excel_project);
            } else {
                println!(
                    "get sheet failed,name: {}, index: {}",
                    sheet_names[i], index
                );
            }
        }
    } else {
        println!("read file failed, {}", path);
        return Err(format!("read file failed, {}", path));
    }
    Ok(projects)
}

fn parse_cell_string(data: String) -> String {
    trim_string(data)
}

fn parse_cell_f64(data: String) -> f64 {
    data.parse::<f64>().or::<f64>(Ok(0.0)).unwrap()
}

fn check_title_mapping(title_name: &str, conf: &Conf) -> Result<String, String> {
    for (k, v) in &conf.column_mapping {
        // let matchs = title_name
        //     .matches(v)
        //     .map(|s| s.to_string())
        //     .collect::<Vec<String>>();
        // if matchs.len() == 0 {
        //     continue;
        // }
        if title_name.eq(v) {
            println!("match title, name: {}", title_name);
            return Ok(k.clone());
        }
    }
    // println!("not match title, name: {}", title_name);
    Err("invalid title name".to_string())
}

fn trim_string(str: String) -> String {
    if str.starts_with("\"") && str.ends_with("\"") {
        str.strip_prefix("\"")
            .unwrap()
            .strip_suffix("\"")
            .unwrap()
            .to_string()
    } else {
        str
    }
}

#[derive(Debug)]
struct ExcelProject {
    project_name: String,
    records: Vec<ExcelRecord>,
}

#[derive(Debug)]
struct ExcelRecord {
    category: String,
    contract_number: String,
    contract_amount: f64,
    material_amount: f64,
    material_verify_amount: f64,
    payment_amount: f64,
    provider_name: String,
    invoice_amount: f64,
    comment: String,
    his_defaulters_amount: f64,
    his_hang_invoice_amount: f64,
}

struct Conf {
    start_row: usize,
    start_sheet: usize,
    column_mapping: Vec<(String, String)>,
}

impl Conf {
    fn new(
        start_row: usize,
        start_sheet: usize,
        column_mapping: Vec<(&'static str, &'static str)>,
    ) -> Self {
        Self {
            start_row,
            start_sheet,
            column_mapping: column_mapping
                .into_iter()
                .map(|(k, v)| (k.to_string(), v.to_string()))
                .collect(),
        }
    }
}

fn excel_projects_to_db_data(
    projects: &Vec<ExcelProject>,
    db: Data<DbPool>,
) -> Result<(), diesel::result::Error> {
    let mut conn = db.get().expect("get conn failed");
    conn.transaction(move |conn| {
        // providers
        let mut provider_names = vec![];
        for proj in projects {
            for record in &proj.records {
                if !provider_names.contains(&record.provider_name) {
                    provider_names.push(record.provider_name.clone());
                }
            }
        }
        let db_providers: Vec<Provider> = provider.load::<Provider>(conn)?;
        let db_pvns = db_providers
            .iter()
            .map(|p| p.name.clone())
            .collect::<Vec<String>>();
        let to_save_pvds = provider_names
            .iter()
            .filter(|pvn| !db_pvns.contains(&pvn))
            .map(|pvn| Provider {
                id: 0,
                name: pvn.clone(),
                contact_name: String::default(),
                contact_phone: String::default(),
                remarks: None,
            })
            .collect::<Vec<Provider>>();
        let save_pvds_size = insert_into(provider::table)
            .values(&to_save_pvds)
            .execute(conn)?;
        println!("import {} providers", save_pvds_size);
        let after_save_providers = provider.load::<Provider>(conn)?;
        let mut provider_map = HashMap::new();
        for pvd in after_save_providers {
            provider_map.insert(pvd.name, pvd.id);
        }

        let mut to_save_materials = vec![];
        let mut to_save_payments = vec![];
        let mut to_save_invoices = vec![];
        // project stuff
        for excel_project in projects {
            let proj = Project {
                id: 0,
                name: excel_project.project_name.clone(),
                remarks: None,
                deadline_time: None,
            };
            let proj_insert_size = insert_into(project::table)
                .values(&vec![proj.clone()])
                .execute(conn)?;
            if proj_insert_size.eq(&0) {
                println!(
                    "insert project {} failed, returen {}",
                    proj.name, proj_insert_size
                );
                return Err(diesel::result::Error::NotFound);
            }
            let the_project: Project = project::table
                .filter(project::name.eq(&proj.name))
                .first::<Project>(conn)?;

            for record in &excel_project.records {
                let the_contract = Contract {
                    id: 0,
                    project_id: the_project.id,
                    provider_id: provider_map.get(&record.provider_name).unwrap().clone(),
                    no: record.contract_number.clone(),
                    sub_contract: 0,
                    parent_no: None,
                    contract_date: None,
                    contract_amount: record.contract_amount,
                    remarks: None,
                };
                insert_into(contract::table)
                    .values(&vec![the_contract])
                    .execute(conn)?;
                let the_db_contract: Contract = contract::table
                    .filter(contract::no.eq(&record.contract_number))
                    .first::<Contract>(conn)?;

                let mut the_material = Material::default();
                the_material.id = 0;
                the_material.contract_id = the_db_contract.id;
                the_material.delivery_date = 0;
                the_material.delivery_numer = None;
                the_material.affiliation = Some(record.provider_name.clone());
                the_material.category = Some(record.category.clone());
                the_material.name = record.category.clone();
                the_material.specification = "".to_string();
                the_material.unit = None;
                the_material.verify_number = 1.0;
                the_material.price = record.material_amount;
                the_material.verify_amount = Some(record.material_verify_amount);
                the_material.comment = None;
                the_material.verify_date = Some(0);
                the_material.verified = Some(1);
                the_material.his_defaulters_amount = Some(record.his_defaulters_amount);
                the_material.his_hang_invoice_amount = Some(record.his_hang_invoice_amount);
                the_material.his_flag = Some(1);
                to_save_materials.push(the_material);

                let the_payment = Payment {
                    id: 0,
                    contract_id: the_db_contract.id,
                    pay_amount: record.payment_amount,
                    pay_date: 0,
                    payer: "".to_string(),
                    special: vec![0],
                    special_payment: None,
                    special_amount: None,
                    remarks: Some(record.comment.clone()),
                };
                to_save_payments.push(the_payment);

                let the_invoice = Invoice {
                    id: 0,
                    contract_id: the_db_contract.id,
                    amount: record.invoice_amount,
                    invoice_date: 0,
                    remarks: None,
                };
                to_save_invoices.push(the_invoice);
            }
        }
        // materials、payments、invoices
        let material_item_size = insert_into(material::table)
            .values(&to_save_materials)
            .execute(conn)?;
        println!("import material: {}", material_item_size);
        // let payment_item_size = insert_into(payment::table)
        //     .values(&to_save_payments)
        //     .execute(conn)?;
        // println!("import payment: {}", payment_item_size);
        // let invoice_item_size = insert_into(invoice::table)
        //     .values(&to_save_invoices)
        //     .execute(conn)?;
        // println!("import invoice: {}", invoice_item_size);
        Ok(())
    })
}

#[cfg(test)]
mod tests {
    // use crate::{parse_excel_projects, Conf};

    use crate::api::imports::{parse_excel_projects, Conf};

    #[test]
    fn test_parse() -> Result<(), String> {
        let conf = Conf::new(
            2,
            1,
            vec![
                ("category", "类别"),
                ("contract_number", "合同编号"),
                ("contract_amount", "合同金额"),
                ("material_amount", "材料款"),
                ("material_verify_amount", "结算/对账金额"),
                ("payment_amount", "合计付款"),
                ("provider_name", "材料商名称"),
                ("invoice_amount", "合计已开票"),
                ("comment", "备注"),
            ],
        );
        let excel_projects = parse_excel_projects("./../test.xlsx", &conf)?;
        println!("projects: {:?}", excel_projects);
        Ok(())
    }
}
